package com.home766.dao.impl;

import com.home766.dao.ShopsDao;
import com.home766.dto.ShopsDto;
import com.home766.entity.Shops;
import com.home766.util.StringUtil;
import net.biz.Page;
import net.biz.Pageable;
import net.biz.dao.NativeBaseDao;
import net.biz.dao.impl.BaseDaoImpl;
import net.biz.entity.Area;
import net.biz.util.ParamsUtil;
import org.apache.commons.lang.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository("shopsDaoImpl")
public class ShopsDaoImpl extends BaseDaoImpl<Shops,Long> implements ShopsDao{
	@Autowired
	private NativeBaseDao nativeBaseDao;

	@Override
	public Page<Shops> getShopsList(Shops shops, Pageable pageable, String properties) {
		// TODO Auto-generated method stub
		String sql = " select sp.id as id,sp.address as address,sp.area_id as area_Id,sp.city_id as city_Id,sp.contact_p as contact_P,sp.contact_t as contact_T,sp.gpsx as gps_X,sp.gpsy as gps_Y,"
				+ " sp.gps_address as gps_Address,sp.province_id as province_Id,sp.ranges as ranges,sp.shops_name as shops_Name,sp.store_scale as store_Scale,sp.store_type as store_Type,"
				+ " sp.taxpayer_type as taxpayer_Type,areas.full_name as full_Name, "
				+ " (select group_concat(a1.`name`) from xx_area a1 inner join 766_service_area a2 on a1.id=a2.area_id where sp.shops_code=a2.shops_code) as area_Name,sp.shops_code as shops_Code"
				+ " ,d1.title as store_Type_Name,d2.title as taxpayer_Type_Name,d3.title as store_Scale_Name,ad.username as user_Name,d4.title as shops_Level_Name"
				+ "	from 766_shops sp"
				+ " left join xx_area areas on areas.id= IFNULL(sp.area_id,sp.city_id)"
				+ " left join 766_dictionary d1 on d1.id=sp.store_type"
				+ " left join 766_dictionary d2 on d2.id=sp.taxpayer_type"
				+ " left join 766_dictionary d3 on d3.id=sp.store_scale"
				+ " left join 766_dictionary d4 on d4.id=sp.shops_level"
				+ " left join xx_admin ad on ad.id=sp.admin"
				+ " where 1=1 and (sp.delete_state=0 or sp.delete_state is null)";
		Map<String,Object> tempMap = new HashMap<String, Object>();
		if(StringUtils.isNotEmpty(shops.getShopsName())){
			sql+=" and sp.shops_name like :shopsName";
			tempMap.put("shopsName", "%"+shops.getShopsName()+"%");
		}
		if(shops.getShopsCode()!=null){
			sql+=" and sp.shops_code=:shopsCode";
			tempMap.put("shopsCode",shops.getShopsCode());
		}
		if(shops.getStoreType()!=null){
			sql+=" and sp.store_type=:storeType";
			tempMap.put("storeType",shops.getStoreType());
		}
		if(shops.getProvinceId()!=null){
			sql+=" and sp.province_id=:provinceId";
			tempMap.put("provinceId", shops.getProvinceId());
		}
		if(shops.getCityId()!=null){
			sql+=" and sp.city_id=:cityId";
			tempMap.put("cityId", shops.getCityId());
		}
		if(shops.getAreaId()!=null){
			sql+=" and sp.area_id=:areaId";
			tempMap.put("areaId", shops.getAreaId());
		}
		if(StringUtil.isNotEmpty(properties)){
			sql+=" order by "+properties;
		}
		return nativeBaseDao.findPage(sql, tempMap, pageable,Shops.class);
	}

	@Override
	public List<Area> getShopsArea() {
		// TODO Auto-generated method stub
		String sql = " select DISTINCT area.`name` as name,area.id as id  from xx_area area"
				+ " inner join 766_shops sp on sp.province_id = area.id order by area.`name`";
		Map<String,Object> tempMap = new HashMap<String, Object>();
		return nativeBaseDao.findList(sql, tempMap, Area.class);
	}

	@Override
	public List<Area> getShopsAreaChildren(Long provinceId) {
		// TODO Auto-generated method stub
		String sql = "select DISTINCT area.`name` as name,area.id as id  from xx_area area inner join 766_shops sp on sp.city_id = area.id"
				+ " where sp.province_id=:provinceId order by area.`name`";
		Map<String,Object> tempMap = new HashMap<String, Object>();
		if(provinceId==null){
			return null;
		}
		tempMap.put("provinceId", provinceId);
		return nativeBaseDao.findList(sql, tempMap, Area.class);
	}

	@Override
	public List<Shops> getShopsByAreaId(Long areaId, boolean limit, Integer orderByType, Long storeType, Long shopsNature) {
		// TODO Auto-generated method stub
		String sql = " select sp.id as id,sp.address as address,sp.area_id as area_Id,sp.city_id as city_Id,sp.contact_p as contact_P,sp.contact_t as contact_T,sp.gpsx as gps_X,sp.gpsy as gps_Y,"
				+ " sp.gps_address as gps_Address,sp.province_id as province_Id,sp.ranges as ranges,sp.shops_name as shops_Name,sp.store_scale as store_Scale,sp.store_type as store_Type,"
				+ " sp.taxpayer_type as taxpayer_Type,sp.shops_nature as shops_Nature,sp.shops_code as shops_Code from 766_shops sp"
				+ " inner join xx_area area on area.id=IFNULL(sp.area_id,sp.city_id)"
				+ " where 1=1 and (sp.delete_state=0 or sp.delete_state is null) ";
		if(storeType!=null){
			sql+=" and sp.store_type <> "+ ParamsUtil.SHOP_ORDER_STORE_TYPE;
		}
		Map<String, Object> params=new HashMap<String, Object>();
		if(areaId!=null){
			sql+=" and area.id=:areaId";
			params.put("areaId", areaId);
		}
		if(shopsNature!=null){
			//服务门店
			sql+=" and (sp.shops_nature=1 or sp.shops_nature=2)";
		}else{
			//下单门店
			sql+=" and (sp.shops_nature=0 or sp.shops_nature=2)";
		}
		if(limit){
			if(orderByType!=null){
				if(orderByType== ParamsUtil.SHOP_ORDER_BY_TYPE_SCHEME){
					sql+=" order by sp.service_frequency asc,sp.id asc limit 0,1";
				}else{
					sql+=" order by sp.orders_frequency asc,sp.id asc limit 0,1";
				}
			}
			
		}
		return nativeBaseDao.findList(sql, params, Shops.class);
	}

	@Override
	public List<ShopsDto> getShopsList(Shops shops) {
		// TODO Auto-generated method stub
		String sql = " select sp.id as id,sp.address as address,sp.area_id as area_Id,sp.city_id as city_Id,sp.contact_p as contact_P,sp.contact_t as contact_T,sp.gpsx as gps_X,sp.gpsy as gps_Y,"
				+ " sp.gps_address as gps_Address,sp.province_id as province_Id,sp.ranges as ranges,sp.shops_name as shops_Name,sp.store_scale as store_Scale,sp.store_type as store_Type,"
				+ " sp.taxpayer_type as taxpayer_Type,areas.full_name as full_Name, d4.title as shops_Level_Name,"
				+ " (select group_concat(a1.`name`) from xx_area a1 inner join 766_service_area a2 on a1.id=a2.area_id where sp.shops_code=a2.shops_code) as area_Name,sp.shops_code as shops_Code"
				+ "	,d1.title as store_Type_Name,d2.title as taxpayer_Type_Name,d3.title as store_Scale_Name,ad.username as user_Name from 766_shops sp"
				+ " left join xx_area areas on areas.id= IFNULL(sp.area_id,sp.city_id)"
				+ " left join 766_dictionary d1 on d1.id=sp.store_type"
				+ " left join 766_dictionary d2 on d2.id=sp.taxpayer_type"
				+ " left join 766_dictionary d3 on d3.id=sp.store_scale"
				+ " left join 766_dictionary d4 on d4.id=sp.shops_level"
				+ " left join xx_admin ad on ad.id=sp.admin"
				+ " where 1=1 and (sp.delete_state=0 or sp.delete_state is null)";
		Map<String,Object> tempMap = new HashMap<String, Object>();
		if(StringUtils.isNotEmpty(shops.getShopsName())){
			sql+=" and sp.shops_name like :shopsName";
			tempMap.put("shopsName", "%"+shops.getShopsName()+"%");
		}
		if(StringUtils.isNotEmpty(shops.getAddress())){
			sql+=" and sp.address like :address";
			tempMap.put("address","%"+shops.getAddress()+"%");
		}
		if(StringUtils.isNotEmpty(shops.getContactP())){
			sql+=" and sp.contact_p like :contactP";
			tempMap.put("contactP", "%"+shops.getContactP()+"%");
		}
		if(shops.getTaxpayerType()!=null){
			sql+=" and sp.taxpayer_type=:taxpayer";
			tempMap.put("taxpayer", shops.getTaxpayerType());
		}
		if(shops.getStoreType()!=null){
			sql+=" and sp.store_type=:storeType";
			tempMap.put("storeType",shops.getStoreType());
		}
		if(shops.getStoreScale()!=null){
			sql+=" and sp.store_scale=:storeScale";
			tempMap.put("storeScale", shops.getStoreScale());
		}
		if(shops.getProvinceId()!=null){
			sql+=" and sp.province_id=:provinceId";
			tempMap.put("provinceId", shops.getProvinceId());
		}
		if(shops.getCityId()!=null){
			sql+=" and sp.city_id=:cityId";
			tempMap.put("cityId", shops.getCityId());
		}
		if(shops.getAreaId()!=null){
			sql+=" and sp.area_id=:areaId";
			tempMap.put("areaId", shops.getAreaId());
		}
		if(shops.getShopsCode()!=null){
			sql+=" and sp.shops_code=:shopsCode";
			tempMap.put("shopsCode",shops.getShopsCode());
		}
		return nativeBaseDao.findList(sql, tempMap, ShopsDto.class);
	}

	@Override
	public List<Area> getShopsAreaChildrens(Long cityId) {
		// TODO Auto-generated method stub
		String sql = "select DISTINCT area.`name` as name,area.id as id  from xx_area area inner join 766_shops sp on sp.area_id = area.id"
				+ " where sp.city_id=:cityId order by area.`name`";
		Map<String,Object> tempMap = new HashMap<String, Object>();
		if(cityId==null){
			return null;
		}
		tempMap.put("cityId", cityId);
		return nativeBaseDao.findList(sql, tempMap, Area.class);
	}

	@Override
	public Page<Shops> getShopsReceptionList(Shops shops, Pageable pageable) {
		// TODO Auto-generated method stub
		String sql = " select sp.id as id,sp.address as address,sp.area_id as area_Id,sp.city_id as city_Id,sp.contact_p as contact_P,sp.contact_t as contact_T,sp.gpsx as gps_X,sp.gpsy as gps_Y,"
				+ " sp.gps_address as gps_Address,sp.province_id as province_Id,sp.ranges as ranges,sp.shops_name as shops_Name,sp.store_scale as store_Scale,sp.store_type as store_Type,"
				+ " sp.taxpayer_type as taxpayer_Type,areas.full_name as full_Name,d4.title as shops_Level_Name,"
				+ " (select group_concat(a1.`name`) from xx_area a1 inner join 766_service_area a2 on a1.id=a2.area_id where sp.shops_code=a2.shops_code) as area_Name,sp.shops_code as shops_Code"
				+ " ,d1.title as store_Type_Name,d2.title as taxpayer_Type_Name,d3.title as store_Scale_Name"
				+ "	from 766_shops sp"
				+ " left join xx_area areas on areas.id= IFNULL(sp.area_id,sp.city_id)"
				+ " left join 766_dictionary d1 on d1.id=sp.store_type"
				+ " left join 766_dictionary d2 on d2.id=sp.taxpayer_type"
				+ " left join 766_dictionary d3 on d3.id=sp.store_scale"
				+ " left join 766_dictionary d4 on d4.id=sp.shops_level"
				+ " where 1=1 and d1.title !='加盟专柜' and (sp.delete_state=0 or sp.delete_state is null)";
		Map<String,Object> tempMap = new HashMap<String, Object>();
		if(shops.getStoreType()!=null){
			sql+=" and sp.store_type=:storeType";
			tempMap.put("storeType",shops.getStoreType());
		}
		if(shops.getProvinceId()!=null){
			sql+=" and sp.province_id=:provinceId";
			tempMap.put("provinceId", shops.getProvinceId());
		}
		if(shops.getCityId()!=null){
			sql+=" and sp.city_id=:cityId";
			tempMap.put("cityId", shops.getCityId());
		}
		if(shops.getAreaId()!=null){
			sql+=" and sp.area_id=:areaId";
			tempMap.put("areaId", shops.getAreaId());
		}
		return nativeBaseDao.findPage(sql, tempMap, pageable,Shops.class);
	}

	@Override
	public Shops getSingleShops(Long adminId) {
		// TODO Auto-generated method stub
		String sql = "select shops.id as id from 766_shops shops where shops.admin=:adminId and (shops.delete_state=0 or shops.delete_state is null)";
		Map<String,Object> tempMap = new HashMap<String, Object>();
		tempMap.put("adminId", adminId);
		return nativeBaseDao.findOne(sql, tempMap, Shops.class);
	}

	@Override
	public Shops getServiceTotalShops() {
		// TODO Auto-generated method stub 
		String jpql = " from Shops shops where shops.shopsName='总部售后' and (shops.deleteState=0 or shops.deleteState is null)";
		List<Shops> list = entityManager.createQuery(jpql,Shops.class).getResultList();
		Shops shops = new Shops();
		if(list.size()>0){
			shops=list.get(0);
		}else{
			shops=null;
		}
		return shops;
	}

	@Override
	public List<Shops> getShopsLevel(Long province) {
		// TODO Auto-generated method stub
		String sql = "select sp.id as id,sp.shops_name as shops_Name from 766_shops sp"
				+ " left join 766_dictionary dc on dc.id=sp.shops_level"
				+ " where dc.title='A级'";
		Map<String,Object> tempMap = new HashMap<String, Object>();
		if(province!=null){
			sql+=" and sp.province_id=:province";
			tempMap.put("province",province);
		}
		sql+=" or sp.shops_name='总部售后' order by sp.shops_name='总部售后' desc";
		return nativeBaseDao.findList(sql, tempMap, Shops.class);
	}
}
